﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using SD.IDAL;
using SD.Entities;
using System.Data.SqlClient;
using System.Data;

namespace SD.DAL
{
    public class OrgProgrammeDAO:BaseDAO, IOrgProgrammeDAO
    {
        #region IOrgProgrammeDAO Members

        public List<string> GetByOrganization(string orgId)
        {
            SqlCommand cmd = CreateCommand();
            cmd.CommandText = "OrgProgrammeGetByOrg";
            cmd.Parameters.AddWithValue("@orgId", orgId);

            List<String> lstResult = new List<string>();
            try
            {
                DataTable dtb = ExecuteSelectCommand(cmd);
                // ref id to result list
                foreach (DataRow row in dtb.Rows)
                {
                    lstResult.Add(row["ProgrammeID"].ToString());
                }
            }
            catch (SqlException ex)
            {
                throw ex;
            }

            return lstResult;
        }

        /// <summary>
        /// Insert new Programme
        /// </summary>
        /// <param name="orgId">ID of the organization</param>
        /// <param name="programmeId">ID of the programme</param>
        /// <returns>Indicate whether inserting succeeded</returns>
        public bool Insert(string orgId, string programmeId)
        {
            try
            {
                SqlCommand cmd = CreateCommand();
                cmd.CommandText = "OrgProgrammeInsert";
                cmd.Parameters.AddWithValue("@orgId", orgId);
                cmd.Parameters.AddWithValue("@ProgrammeId", programmeId);

                ExecuteNonQuery(cmd);
            }
            catch (SqlException ex)
            {
                throw ex;
            }

            return true;
        }

        /// <summary>
        /// Update programme
        /// </summary>
        /// <param name="orgId">ID of organization</param>
        /// <param name="detail"></param>
        /// <returns></returns>
        public bool Update(string orgId, List<Programme> newDetails)
        {
            try
            {
                List<String> lstProgramme = GetByOrganization(orgId);

                #region update details 2
                // remove deleted details
                foreach (String strProgrammeID in lstProgramme)
                {
                    Boolean isDeleted = true;
                    // search in new details list
                    foreach (Programme programme in newDetails)
                    {
                        if (strProgrammeID.Equals(programme.ProgrammeId))
                        {
                            isDeleted = false;
                            break;
                        }
                    }

                    if (isDeleted)
                    {
                        Delete(orgId, strProgrammeID);
                    }
                }

                // insert new details
                foreach (Programme programme in newDetails)
                {
                    Boolean isInserted = true;
                    // search in old details
                    foreach (String strProgrammeID in lstProgramme)
                    {
                        // Details is existing
                        if (strProgrammeID.Equals(programme.ProgrammeId))
                        {
                            isInserted = false;
                            break;
                        }
                    }
                    // Not exist in database
                    if (isInserted)
                    {
                        Insert(orgId, programme.ProgrammeId);
                    }
                }
                #endregion
            }
            catch (SqlException ex)
            {
                throw ex;
            }

            return true;
        }

        /// <summary>
        /// Delete programme of an organization
        /// </summary>
        /// <param name="orgId">ID of the organization</param>
        /// <param name="refId">ID of the programme</param>
        /// <returns>Indicate whether deleting succeeded</returns>
        public bool Delete(string orgId, string programmeId)
        {
            try
            {
                SqlCommand cmd = CreateCommand();
                cmd.CommandText = "OrgProgrammeDelete";
                cmd.Parameters.AddWithValue("@orgId", orgId);
                cmd.Parameters.AddWithValue("@ProgrammeId", programmeId);

                ExecuteNonQuery(cmd);
            }
            catch (SqlException ex)
            {
                throw ex;
            }

            return true;
        }

        #endregion
    }
}
